﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using GenericTools.Models;
using DataModels.Common;

namespace GenericTools
{
    public class Querying
    {
        #region Class Constants
        #region Types of Queries
        const int QUERY_TYPE_VIEW = 1;
        const int QUERY_TYPE_FUNCTION = 2;
        const int QUERY_TYPE_STORED_PROCEDURE = 3;
        const int QUERY_TYPE_PARAMETERIZED_VIEW = 4;
        #endregion

        #region Types of Commands
        public const int SQLCOMMAND_TEXT = 19891;  // magic numbers
        public const int SQLCOMMAND_STORED_PROCEDURE = 19892;
        public const int SQLCOMMAND_TABLE_DIRECT = 19893;
        #endregion

        #region Database DataType
        public const int DBTYPE_INTEGER = 19941;
        public const int DBTYPE_DECIMAL = 19942;
        public const int DBTYPE_STRING = 19943;
        public const int DBTYPE_DATETIME = 19944;
        public const int DBTYPE_BOOLEAN = 19945;
        #endregion
        #endregion

        #region Class Static Variables
        private static string m_DataProvider = "System.Data.SqlClient";
        private static string m_ConnectionString = "Data Source=(local);Initial Catalog=IQSMS_TestDB;User ID=sa;Password=changed";
        #endregion

        /// <summary>
        /// Gets the list of all enabled [not deleted] categories
        /// </summary>
        /// <returns>DataTable object containing categories</returns>
        public static DataTable GetAvailableCategories()
        {
            DbCommand command = GenericDataAccess.CreateCommand(m_DataProvider, m_ConnectionString);
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT [QueryCategoryID],[Name],[Description] FROM QueryCategories WHERE [Deleted]=0";
            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            return dataTable;            
        }

        /// <summary>
        /// Gets the list of all enable [not deleted] queries
        /// </summary>
        /// <returns>DataTable object containing queries</returns>
        public static DataTable GetAvailableQueries()
        {
            DbCommand command = GenericDataAccess.CreateCommand(m_DataProvider, m_ConnectionString);
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT [QueryID],[Name],[InternalName],[Description] FROM Queries WHERE [Deleted]=0";
            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            return dataTable;       
        }

        /// <summary>
        /// Gets the queries that belong to category categoryId
        /// </summary>
        /// <param name="categoryId">DataTable object containing queries</param>
        /// <returns></returns>
        public static DataTable GetQueriesByCategory(int categoryId)
        {
            DbCommand command = GenericDataAccess.CreateCommand(m_DataProvider, m_ConnectionString);
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT [q].[QueryID],[q].[Name],[q].[InternalName],[q].[Description],[q].[Definition] 
                FROM [Queries] AS [q] JOIN [CategorizingQueries] AS [cq] ON [q].[QueryID]=[cq].[QueryID] 
                WHERE [q].[Deleted]=0 AND [cq].[QueryCategoryID]=@QueryCategoryID";
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@QueryCategoryID";
            param.Value = categoryId;
            param.DbType = DbType.Int32;
            command.Parameters.Add(param);
            
            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            return dataTable;   
        }

        /// <summary>
        /// Gets the query category details identified by query category categoryId
        /// </summary>
        /// <param name="categoryId">DataTable object containing query category</param>
        /// <returns></returns>
        public static DataTable GetCategoryDetails(int categoryId)
        {
            DbCommand command = GenericDataAccess.CreateCommand(m_DataProvider, m_ConnectionString);
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT [QueryCategoryID],[Name],[Description] FROM QueryCategories 
                WHERE [QueryCategoryID]=@QueryCategoryID";
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@QueryCategoryID";
            param.Value = categoryId;
            param.DbType = DbType.Int32;
            command.Parameters.Add(param);

            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            return dataTable;
        }

        /// <summary>
        /// Gets the query categories which the query queryId belongs
        /// </summary>
        /// <param name="queryId">DataTable object containing query categories</param>
        /// <returns></returns>
        public static DataTable GetQueryCategories(int queryId)
        {
            DbCommand command = GenericDataAccess.CreateCommand(m_DataProvider, m_ConnectionString);
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT [c].[QueryCategoryID],[c].[Name],[c].[Description] FROM QueryCategories AS c
                JOIN CategorizingQueries AS cq ON cq.QueryCategoryId=c.QueryCategoryID
                WHERE cq.QueryID=@QueryID AND c.Deleted=0";
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@QueryID";
            param.Value = queryId;
            param.DbType = DbType.Int32;
            command.Parameters.Add(param);

            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            return dataTable;
        }

        /// <summary>
        /// Gets the details of query identified by queryId
        /// </summary>
        /// <param name="queryId">Unique identifier of query</param>
        /// <returns>DataTable object containing query details</returns>
        public static DataTable GetQueryDetails(int queryId)
        {
            DbCommand command = GenericDataAccess.CreateCommand(m_DataProvider, m_ConnectionString);
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT [q].[QueryID],[q].[Name],[q].[InternalName],[q].[Description],[q].[Definition], 
                [q].[QueryTypeId] FROM [Queries] AS [q] WHERE [q].[QueryID]=@QueryID";
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@QueryID";
            param.Value = queryId;
            param.DbType = DbType.Int32;
            command.Parameters.Add(param);

            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            return dataTable;   
        }

        /// <summary>
        /// Removes a query identified by queryId from category identified by categoryId
        /// </summary>
        /// <param name="queryId">Query Unique Identifier</param>
        /// <param name="categoryId">Query Category Unique Identifier</param>
        /// <returns></returns>
        public static bool RemoveQueryFromCategory(int queryId, int categoryId)
        {
            DbCommand command = GenericDataAccess.CreateCommand(m_DataProvider, m_ConnectionString);
            command.CommandType = CommandType.Text;
            command.CommandText = @"DELETE FROM CategorizingQueries 
                WHERE [QueryID]=@QueryID AND [QueryCategoryID]=@QueryCategoryID";
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@QueryID";
            param.Value = queryId;
            param.DbType = DbType.Int32;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@QueryCategoryID";
            param.Value = categoryId;
            param.DbType = DbType.Int32;
            command.Parameters.Add(param);

            int affectedRows = GenericDataAccess.ExecuteNonQuery(command);
            return (affectedRows > 0);
        }

        /// <summary>
        /// Adds query queryId into query category categoryId
        /// </summary>
        /// <param name="queryId">Query Unique Identifier</param>
        /// <param name="categoryId">Query Category Unique Identifier</param>
        /// <param name="userId">Unique Identifier of logged user who performs the action</param>
        /// <returns>True on success otherwise false</returns>
        public static bool AddQueryIntoCategory(int queryId, int categoryId, int userId)
        {
            DbCommand command = GenericDataAccess.CreateCommand(m_DataProvider, m_ConnectionString);
            command.CommandType = CommandType.Text;
            command.CommandText = @"INSERT INTO CategorizingQueries(QueryId, QueryCategoryID, CreateDate, CreatedByUser)
                VALUES(@QueryID,@QueryCategoryID, @CreateDate, @CreatedByUser)";
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@QueryID";
            param.Value = queryId;
            param.DbType = DbType.Int32;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@QueryCategoryID";
            param.Value = categoryId;
            param.DbType = DbType.Int32;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@CreateDate";
            param.Value = DateTime.Now;
            param.DbType = DbType.DateTime;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@CreatedByUser";
            param.Value = userId;
            param.DbType = DbType.Int32;
            command.Parameters.Add(param);

            int affectedRows = GenericDataAccess.ExecuteNonQuery(command);
            return (affectedRows > 0);
        }

        /// <summary>
        /// Deletes the query category categoryId
        /// </summary>
        /// <param name="categoryId">Query Category Unique identifier</param>
        /// <param name="userId">Unique Identifier of logged user who performs the action</param>
        /// <returns>True on success otherwise false</returns>
        public static bool DeleteCategory(int categoryId, int userId)
        {
            DbCommand command = GenericDataAccess.CreateCommand(m_DataProvider, m_ConnectionString);
            command.CommandType = CommandType.Text;
            command.CommandText = @"UPDATE QueryCategories SET Deleted = 1, DeleteDate=@DeleteDate, 
                DeletedByUser=@DeletedByUser WHERE QueryCategoryId=@QueryCategoryID";
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@QueryCategoryID";
            param.Value = categoryId;
            param.DbType = DbType.Int32;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@DeleteDate";
            param.Value = DateTime.Now;
            param.DbType = DbType.DateTime;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@DeletedByUser";
            param.Value = userId;
            param.DbType = DbType.Int32;
            command.Parameters.Add(param);

            int affectedRows = GenericDataAccess.ExecuteNonQuery(command);
            return (affectedRows > 0);
        }

        public static bool SaveQuery(string name, string description, string definition,
            string internalName, TypeOfQuery type, bool createTable, int userId)
        {
            int typeOfQuery = 1;
            string connectionString = AppConfiguration.DBConnectionString;
            DbCommand command = GenericDataAccess.CreateCommand(m_DataProvider, m_ConnectionString);
            command.CommandType = CommandType.Text;

            switch (type)
            {
                case TypeOfQuery.FUNCTION:
                    typeOfQuery = QUERY_TYPE_FUNCTION;
                    break;

                case TypeOfQuery.STORED_PROCEDURE:
                    typeOfQuery = QUERY_TYPE_STORED_PROCEDURE;
                    break;

                case TypeOfQuery.VIEW:
                    typeOfQuery = QUERY_TYPE_VIEW;
                    command.CommandText = String.Format("CREATE VIEW {0} AS {1}", internalName, definition);
                    GenericDataAccess.ExecuteNonQuery(command, false);
                    break;

                case TypeOfQuery.PARAMETERIZED_VIEW:
                    typeOfQuery = QUERY_TYPE_PARAMETERIZED_VIEW;
                    break;
            }

            command.CommandText = @"INSERT INTO Queries([Name],[Description],[Definition],[InternalName],[CreatedByUser],
                [CreateDate],[Deleted],[CreateTable],[QueryTypeId]) VALUES(@Name,@Description,@Definition,@InternalName,
                @CreatedByUser,@CreateDate,@Deleted,@CreateTable,@QueryTypeId)";
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@Name";
            param.DbType = DbType.String;
            param.Value = name;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Description";
            param.DbType = DbType.String;
            param.Value = description;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Definition";
            param.DbType = DbType.String;
            param.Value = definition;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@InternalName";
            param.DbType = DbType.String;
            param.Value = internalName;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@CreatedByUser";
            param.DbType = DbType.Int32;
            param.Value = userId;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@CreateDate";
            param.DbType = DbType.DateTime;
            param.Value = DateTime.Now;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Deleted";
            param.DbType = DbType.Boolean;
            param.Value = false;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@CreateTable";
            param.DbType = DbType.Boolean;
            param.Value = createTable;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@QueryTypeId";
            param.DbType = DbType.Int32;
            param.Value = typeOfQuery;
            command.Parameters.Add(param);

            GenericDataAccess.ExecuteNonQuery(command);
            return true;
        }


        public static bool QueryInternalNameAvailable(string internalName)
        {
            DbCommand command = GenericDataAccess.CreateCommand(m_DataProvider, m_ConnectionString);
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT COUNT(*) FROM Queries WHERE InternalName=@InternalName";
            
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@InternalName";
            param.Value = internalName;
            param.DbType = DbType.String;
            command.Parameters.Add(param);

            string results = GenericDataAccess.ExecuteScalar(command);
            int affectedRows = int.Parse(results);
            return (affectedRows == 0);
        }

        /// <summary>
        /// Updates the query category categoryId details
        /// </summary>
        /// <param name="categoryId">Query Category Unique Identifier</param>
        /// <param name="name">New Query Category</param>
        /// <param name="description">New Query Description</param>
        /// <param name="userId">Unique Identifier of logged user who performs the action</param>
        /// <returns>True on success otherwise false</returns>
        public static bool UpdateCategory(int categoryId, string name, string description, int userId)
        {
            DbCommand command = GenericDataAccess.CreateCommand(m_DataProvider, m_ConnectionString);
            command.CommandType = CommandType.Text;
            command.CommandText = @"UPDATE QueryCategories SET [Name] = @Name, [Description]=@Description,
                UpdateDate=@UpdateDate, UpdatedByUser=@UpdatedByUser WHERE QueryCategoryId=@QueryCategoryID";
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@QueryCategoryID";
            param.Value = categoryId;
            param.DbType = DbType.Int32;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Name";
            param.Value = name;
            param.DbType = DbType.String;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Description";
            param.Value = description;
            param.DbType = DbType.String;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@UpdateDate";
            param.Value = DateTime.Now;
            param.DbType = DbType.DateTime;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@UpdatedByUser";
            param.Value = userId;
            param.DbType = DbType.Int32;
            command.Parameters.Add(param);

            int affectedRows = GenericDataAccess.ExecuteNonQuery(command);
            return (affectedRows > 0);
        }

        /// <summary>
        /// Add query category
        /// </summary>
        /// <param name="name">Name of query category</param>
        /// <param name="description">Description of query category</param>
        /// <param name="userId">Unique Identifier of logged user who performs the action</param>
        /// <returns>True on success otherwise false</returns>
        public static bool AddQueryCategory(string name, string description, int userId)
        {
            DbCommand command = GenericDataAccess.CreateCommand(m_DataProvider, m_ConnectionString);
            command.CommandType = CommandType.Text;
            command.CommandText = @"INSERT INTO QueryCategories([Name], [Description], [CreatedByUser],[CreateDate])
                VALUES(@Name, @Description, @CreatedByUser, @CreateDate)";
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@Name";
            param.Value = name;
            param.DbType = DbType.String;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Description";
            param.Value = description;
            param.DbType = DbType.String;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@CreateDate";
            param.Value = DateTime.Now;
            param.DbType = DbType.DateTime;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@CreatedByUser";
            param.Value = userId;
            param.DbType = DbType.Int32;
            command.Parameters.Add(param);

            int affectedRows = GenericDataAccess.ExecuteNonQuery(command);
            return (affectedRows > 0);
        }

        /// <summary>
        /// Deletes query queryId
        /// </summary>
        /// <param name="queryId">Query unique identifier</param>
        /// <param name="userId">Unique Identifier of logged user who performs the action</param>
        /// <returns>True on success otherwise false</returns>
        public static bool DeleteQuery(int queryId, int userId)
        {
            DbCommand command = GenericDataAccess.CreateCommand(m_DataProvider, m_ConnectionString);
            command.CommandType = CommandType.Text;
            command.CommandText = @"UPDATE Queries SET Deleted = 1, DeleteDate=@DeleteDate, 
                DeletedByUser=@DeletedByUser WHERE QueryCategoryId=@QueryID";
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@QueryID";
            param.Value = queryId;
            param.DbType = DbType.Int32;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@DeleteDate";
            param.Value = DateTime.Now;
            param.DbType = DbType.DateTime;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@DeletedByUser";
            param.Value = userId;
            param.DbType = DbType.Int32;
            command.Parameters.Add(param);

            int affectedRows = GenericDataAccess.ExecuteNonQuery(command);
            return (affectedRows > 0);
        }

        public static bool CreateTableFromView(string viewName, string viewDefinition)
        {
            int offset = viewDefinition.IndexOf("FROM", 0, StringComparison.OrdinalIgnoreCase);
            string p1 = viewDefinition.Substring(0, offset);
            string p2 = viewDefinition.Substring(offset);
            string tableName = String.Format("tbl_{0}", viewName);
            string createQuery = String.Format("{0} INTO {1} {2}", p1, tableName, p2); 

            DbCommand command = GenericDataAccess.CreateCommand(m_DataProvider, m_ConnectionString);
            command.CommandType = CommandType.Text;
            //try
            //{
            //    command.CommandText = String.Format("DROP TABLE {0}", tableName);
            //    GenericDataAccess.ExecuteNonQuery(command, false);
            //}
            //catch { }
            try
            {
                command.CommandText = createQuery;
                GenericDataAccess.ExecuteNonQuery(command, false);
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
            }
        }

        /// <summary>
        /// Updates details of query queryId
        /// </summary>
        /// <param name="queryId">Query unique identifier</param>
        /// <param name="name">New name of query</param>
        /// <param name="description">New description of query</param>
        /// <param name="definition">New definition of query</param>
        /// <param name="userId">Unique Identifier of logged user who performs the action</param>
        /// <returns>True on success otherwise false</returns>
        public static bool UpdateQueryDetails(int queryId, string name, string description, 
            string internalName, string definition, int userId)
        {
            DbCommand command = GenericDataAccess.CreateCommand(m_DataProvider, m_ConnectionString);
            command.CommandType = CommandType.Text;
            command.CommandText = String.Format("DROP VIEW {0}", internalName);
            try
            {
                GenericDataAccess.ExecuteNonQuery(command, false);
                //command.ExecuteNonQuery();
            }
            catch { }

            if (definition.Contains("@"))
            {
                command.CommandText = String.Format("CREATE VIEW {0} AS {1}", internalName, definition);
                try
                {
                    GenericDataAccess.ExecuteNonQuery(command, false);
                    //command.ExecuteNonQuery();
                }
                catch
                {
                    return false;
                }
            }            

            command.CommandText = @"UPDATE Queries SET [Name]=@Name, [Description]=@Description,
                [Definition]=@Definition, UpdateDate=@UpdateDate, UpdatedByUser=@UpdatedByUser 
                WHERE QueryID=@QueryID";
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@QueryID";
            param.Value = queryId;
            param.DbType = DbType.Int32;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Name";
            param.Value = name;
            param.DbType = DbType.String;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Description";
            param.Value = description;
            param.DbType = DbType.String;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@Definition";
            param.Value = definition;
            param.DbType = DbType.String;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@UpdateDate";
            param.Value = DateTime.Now;
            param.DbType = DbType.DateTime;
            command.Parameters.Add(param);

            param = command.CreateParameter();
            param.ParameterName = "@UpdatedByUser";
            param.Value = userId;
            param.DbType = DbType.Int32;
            command.Parameters.Add(param);

            int affectedRows = GenericDataAccess.ExecuteNonQuery(command);
            return (affectedRows > 0);
        }

        #region Public Properties
        public static string DataProvider
        {
            get
            {
                return m_DataProvider;
            }
            set
            {
                m_DataProvider = value;
            }
        }

        public static string ConnectionString
        {
            get
            {
                return m_ConnectionString;
            }
            set
            {
                m_ConnectionString = value;
            }
        }
        #endregion

        
    }
}
